package com.yc.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.yc.common.handler.MapResultHandler;
import com.yc.entity.StudentInfo;
import com.yc.entity.User;
import com.yc.vo.StudentInfoAndClassInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface UserMapper extends BaseMapper<User> {

    /**
     * 查询老师领导姓名
     * @param username
     * @return
     */
    @Select("SELECT password,wxpic,nickname,username,(SELECT GROUP_CONCAT( rolecode SEPARATOR ',' ) AS rolecode FROM t_role WHERE\n" +
            " instr( REPLACE ( REPLACE (REPLACE ( role, '[', '' ), ']', '' ), ' ', '' ), id )) as rolecode FROM systemuser WHERE username = #{username}")
    Map<String,Object> queryMan(@Param("username") String username);

    /**
     *查用户的Appid
     */
    @Select("select appid from student_info where stuid = #{stuid}")
    String queryAppid(@Param("stuid") String stuid);

    /**
     *查教师的Appid
     */
    @Select("select appid from systemuser where username = #{username}")
    String queryAppidByTea(@Param("username") String username);

    /**
     * 根据openID查询systemuser有没有微信信息
     */
    @Select("select appid,wxpic,imei from systemuser where username = #{username}")
    User selectByusername(@Param("username") String username);

    /**
     * 添加微信授权数据到systemuser 根据username添加
     */
    @Insert("update systemuser set appid= #{appid},wxpic = #{wxpic},imei = #{imei} where username = #{username}")
    void insertByusername(@Param("appid") String appid,@Param("wxpic") String wxpic,@Param("imei") String imei,@Param("username")String username);

    /**
     * 根据唯一openid查出学生信息
     */
    @Select("select stuid  from student_info where appid = #{openid}")
    String queryown(@Param("openid") String openid);

    /**
     * 查询教师工号
     * @return
     */
    @Select("SELECT username,(SELECT GROUP_CONCAT( rolecode SEPARATOR ',' ) AS rolecode FROM t_role WHERE instr( REPLACE ( REPLACE (REPLACE ( role, '[', '' ), ']', '' ), ' ', '' ), id )) as rolecode FROM systemuser WHERE appid = #{appid}")
    Map<String,Object> queryownBysys(@Param("appid") String openid);
    /**
     * 教师头像不一致修改
     * @param wxpic
     * @param username
     */
    @Update("update systemuser set wxpic = #{wxpic} where username =#{username}")
    void updateBySysWxpic(@Param("wxpic") String wxpic,@Param("username")String username);

    /**
     * 查出密码
     * @param stuid
     * @return
     */
    @Select("select password from systemuser where username =#{username}")
    String queryPwd(@Param("username") String stuid);

    /**
     * 修改系统用户信息
     *
     * @param username
     * @param nickname
     * @param phone
     */
    @Update("UPDATE systemuser set nickname = #{nickname} , phone=#{phone} where username=#{username} ")
    void updateInfo(String username, String nickname, String phone);

    /**
     * 查询老师的课程总数一学年
     * @param school_year
     * @param cname
     * @return
     */
    List<Map<String,Object>> queryUserClassTotalByschoolYear( @Param("school_year") String school_year, @Param("cname") String cname );

    /**
     * 查询老师的课程总数上学期
     * @param school_year
     * @param cname
     * @return
     */
    List<Map<String,Object>> queryUserClassTotalByschoolYearUp( @Param("school_year") String school_year, @Param("cname") String cname );

    /**
     * 查询老师的课程总数下学期
     * @param school_year
     * @param cname
     * @return
     */
    List<Map<String,Object>> queryUserClassTotalByschoolYearDown( @Param("school_year") String school_year, @Param("cname") String cname );
    /**
     * 批量插入
     *
     * @param list
     */
    void batchInsert(List<User> list);


    /**
     * 根据辅导员工号查询id
     *
     * @param nickname
     * @return
     */
    @Select("select id from systemuser where nickname=#{nickname} and username=#{username}")
    Integer selectIdByNickName(@Param("nickname") String nickname,@Param("username") String username);

    /**
     * 查询所有系统用户信息（username）
     *
     * @param username
     * @return
     */
    @Select("select * from systemuser where username=#{username}")
    User selectAllByUsername(String username);

    /**
     * 更改密码
     *
     * @param password
     * @param username
     */
    @Select("update systemuser set  password = #{password} where username=#{username}")
    void updatePasswordByUsername(String password, String username);


    /**
     * 查该用户所在的学院名
     *
     * @return
     */
    @Select("select tname from labeldept where tid=#{tid}")
    List<String> selectDeptNameByUdid(Long tid);


    /**
     * 根据用户名查询用户信息和权限
     * @param username
     * @return
     */
    @Select(" SELECT id,username,nickname,role,udid,tid,(SELECT GROUP_CONCAT( rolecode SEPARATOR ',' ) AS rolecode FROM t_role WHERE instr( REPLACE ( REPLACE (REPLACE ( role, '[', '' ), ']', '' ), ' ', '' ), id )) as rolecode FROM systemuser WHERE username = #{username} ")
    User selectUserInfoAndRole(@Param("username") String username);

    /**
     * 根据工号查询辅导员姓名
     * @param username
     * @return
     */
    @Select("select * from systemuser where username = #{username} ")
    User selectNickNameByUsername(@Param("username") String username);

    /**
     * 获取所有系统用户的   用户名 和 昵称
     * @param mapResultHandler 查询的map集合
     */
    void selectAllUserMap(MapResultHandler<String, String> mapResultHandler);

    /**
     * 教职工修改密码
     * @param pwd
     * @param username
     */
    @Update("update systemuser set password = #{pwd} where username = #{username}")
    void updatePwdBySys(@Param("pwd") String pwd,@Param("username") String username);

    /**
     * 批量添加系统用户
     * @param userList 表格中的用户集合
     * @return
     */
    int addUserList(List<User> userList);

    /**
     * 根据code获得openId,组数据库中查找
     */
    @Select("select appid,stuid from student_info where appid =#{appid}")
    Map<String,Object> queryOpenId(@Param("appid") String openId);

    /**
     * 根据code获得openId,组数据库中查找
     */
    @Select("select appid,username,(select concat(rolecode,',') from t_role where id in (REPLACE(REPLACE(REPLACE(role, '[', ''), ']', ''), ' ', ''))) as rolecode from systemuser where appid =#{appid}")
    Map<String,Object> queryOpenIdBySys(@Param("appid") String openId);

    /**
     * 学生查询openId唯一
     * @param openId
     * @return
     */
    @Select("SELECT count(appid) from student_info  where appid = #{appid}")
    int queryCountByStu(@Param("appid") String openId);

    /**
     * 老师查openId唯一
     * @param openId
     * @return
     */
    @Select("SELECT count(appid) from systemuser  where appid = #{appid}")
    int queryCountBySys(@Param("appid") String openId);

    /**
     * 查询所有系统用户
     * @param username
     * @param nickname
     * @param role
     * @return
     */
    Page<User> selectAllUser(Page<User> page, String username, String nickname, String role);

    /**
     * 查询老师一年的课程
     * @param school_year
     * @return
     */
    List<Map<String,Object>> selectByteaYear(@Param("school_year") String school_year,
                                             @Param("mname")String mname,@Param("cname") String cname,
                                             @Param("yearnumber") String yearnumber);

    /**
     * 查询老师上学期的课程
     * @param school_year
     * @return
     */
    List<Map<String,Object>> selectByteaUpYear(@Param("school_year") String school_year,
                                               @Param("mname")String mname,@Param("cname") String cname,
                                               @Param("yearnumber")String  yearnumber);

    /**
     * 查询老师下学期的课程
     * @param school_year
     * @return
     */
    List<Map<String,Object>> selectByteaDownYear(@Param("school_year") String school_year,
                                                 @Param("mname")String mname,@Param("cname") String cname,
                                                 @Param("yearnumber") String yearnumber);

    List<Map<String,Object>> selectByteaYearBywx(@Param("school_year") String school_year,
                                                 @Param("mname")String mname,@Param("cname") String cname,
                                                 @Param("yearnumber") String yearnumber);
    /**
     * 查当天时间对应的学期
     * @param nowdays
     * @return
     */
    @Select("SELECT school_year from school_year WHERE time = #{time} ")
    String queryNowTerm( @Param("time") String nowdays);

    @Select("SELECT DISTINCT school_year from school_year")
    List<String> queryNowTermAll();

    /**
     * 查询角色,用户名以及工号
     * @return
     */
    @Select("select role as roles,username,nickname from systemuser")
    List<User> selectListUser();

    /**
    *@Description 查询指定老师本月以及本周所考勤的数量
    *@Return
    *@Author dm
    *@Date Created in 2023/8/2 9:15
    **/
    Integer queryHitMonth(@Param("tid")String tid,@Param("time")String time);

    /**
    *@Description 查询本周的考勤次数
    *@Return
    *@Author dm
    *@Date Created in 2023/8/2 9:31
    **/
    Integer queryHisWeek(@Param("tid")String tid,@Param("days")List<String> days);

    /**
     * 查询所有学期
     * @return
     */
    @Select("select DISTINCT school_year from school_year")
    List<String> queryTerms();

    /**
     * 小程序查今天在哪个学期
     */
    @Select("select DISTINCT school_year from school_year where time =#{time}")
    Map<String,Object> queryTerm(@Param("time") String date);

    /**
     * 课时量排名
     * @param school_year
     * @param mname
     * @return
     */
    List<Map<String,Object>> Workload_ranking (@Param("school_year") String school_year,@Param("mname") String mname);

    /**
     * 查前20名问题班级
      * @return
     */
    List<Map<String,Object>> query_question_class();

    /**
     * 根据老师昵称查询id
     * @param nickName
     * @return 老师id
     */
    @Select("select id from systemuser where nickname = #{nickname}")
    Integer selectIdByNickName1(String nickName);





}
